package com.ck.texting.util;

import android.content.Context;

import com.ck.texting.bean.SalaryMsg;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.PatternSyntaxException;

public class ExcelUtil {
    private XSSFSheet ExcelWSheet;
    private XSSFWorkbook ExcelWBook;
    private XSSFCell Cell;
    private XSSFRow row;
    private String filePath;
    public static String errmsg = null;

    //设定要操作的Excel 的文件路径和Excel 文件中的sheet名称
    //在读写excel的时候，均需要先调用此方法，设定要操作的excel 文件路径和要操作的sheet名称
    //构造函数初始化
    public ExcelUtil(String Path, String SheetName) throws Exception {
        FileInputStream ExcelFile;

        try {
            //实例化excel 文件的FileInputStream 对象
            ExcelFile = new FileInputStream(Path);
            //实例化excel 文件的XSSFWorkbook 对象
            ExcelWBook = new XSSFWorkbook(ExcelFile);
            //实例化ExcelWSheet 对象，指定excel 文件中的sheet 名称，后续用于sheet 中行、列和单元格的操作
            ExcelWSheet = ExcelWBook.getSheet(SheetName);
        } catch (Exception e) {
            throw (e);
        }
        filePath = Path;
    }

    //读取指定单元格的数据。需要传入行数、列数
    public String getCellData(int RowNum, int ColNum) throws Exception {
        try {
            Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);
            String CellData = "";
            if (Cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                CellData = Cell.getStringCellValue();
            } else if (Cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
                DecimalFormat df = new DecimalFormat("0");
                CellData = df.format(Cell.getNumericCellValue());
            }
            return CellData;
        } catch (Exception e) {
            e.printStackTrace();
            return "";
        }
    }

    //读取指定单元格的数据且值为字符串。需要传入行数、列数
    public String getCellStrData_value(int RowNum, int ColNum) throws Exception {
        try {
            Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);
            if (Cell != null) {
                String CellData = Cell.getStringCellValue();
                return CellData;
            } else {
                return "";
            }
        } catch (Exception e) {
            return "";
        }
    }

    //读取指定单元格的数据且值为数字。需要传入行数、列数
    public String getCellNumData_value(int RowNum, int ColNum) throws Exception {
        String cellValue = "";
        DecimalFormat df = new DecimalFormat("#");
        try {
            Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);
            if (Cell != null) {
                if (Cell.getCellType() == 1) {
                    cellValue = Cell.getStringCellValue();
                } else {
                    cellValue = String.valueOf(Cell.getNumericCellValue());
                    if (cellValue.endsWith(".0")) {
                        cellValue = cellValue.substring(0, cellValue.length() - 2);
                    }
                }
            }
            return cellValue;
        } catch (Exception e) {
            return "";
        }
    }

    // 从excel 文件获取测试数据的静态方法
    public static List<SalaryMsg> readExcel(Context context, String execlFilePath, String sheetName) throws IOException {
        //File file = new File(execlFilePath);
        FileInputStream inputStream = new FileInputStream(execlFilePath);


        Workbook Workbook = null;

        String fileExtensionName = execlFilePath.substring(execlFilePath.indexOf("."));

        if (fileExtensionName.equals(".xlsx")) {
            Workbook = new XSSFWorkbook(inputStream);
            errmsg = "只支持xls格式文件!";
            return null;
        } else if (fileExtensionName.equals(".xls")) {
            Workbook = new HSSFWorkbook(inputStream);
        }

        Sheet sheet = Workbook.getSheetAt(0);

        if (null == sheet) {
            errmsg = "第一张表错误!";
            return null;
        }

        // 注意：excel 文件的行号和列号都是从0开始
        int rowCount = sheet.getLastRowNum() - sheet.getFirstRowNum();

        List<SalaryMsg> list = new ArrayList<>();

        Row field = sheet.getRow(0);
        List<String> fieldList = new ArrayList<>();
        for (int k = 0; k < field.getLastCellNum(); k++) {// getLastCellNum，是获取最后一个不为空的列是第几个
            if (field.getCell(k) != null) { // getCell 获取单元格数据

                fieldList.add(field.getCell(k).toString());
            } else {
                errmsg = "表头错误!";
                return null;
            }

        }
        // 第二行开始遍历
        for (int i = 1; i < rowCount + 1; i++) {
            // 使用getRow 方法获取行对象
            Row row = sheet.getRow(i);
            //每一格
            String phoneNumber = null;
            String content = null;
            for (int k = 0; k < row.getLastCellNum(); k++) { // getLastCellNum，是获取最后一个不为空的列是第几个
                Cell cell = row.getCell(k);
                cell.setCellType(CellType.STRING);

                if (cell != null) { // getCell 获取单元格数据

                    if (k == 0) {
                        phoneNumber = cell.toString();
                        continue;
                    } else {
                        content += fieldList.get(k) + ":" + cell.toString() + ";";
                    }

                } else {
                    errmsg = ++i + "行" + ++k + "列单元格错误!";
                    return null;
                }
            }

            if (null == phoneNumber) {
                errmsg = ++i + "行手机号错误!";
                return null;
            }
            if (!isChinaPhoneLegal(phoneNumber)) {
                errmsg = ++i + "行手机号非法!";
                return null;
            }
            content = content.replace("null", "");
            content = content.trim();
            list.add(new SalaryMsg(phoneNumber, content));

        }
        // 关闭excel 文件
        return list;
    }

    public int getLastCellNum() {
        // 返回数据文件的最后一列的列号，如果有12列，则结果返回11
        return ExcelWSheet.getRow(0).getLastCellNum() - 1;
    }

    public static boolean isChinaPhoneLegal(String str) throws PatternSyntaxException {
        return str.length() == 11;
    }

}
